﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class Client, define los datos de un usuario del sistema
/// </summary>

#region Class

public class clsClient : DAC {
    #region Variables

    private int idClient, idPLegal, idUser;
    private string sName;
    private const String tabla = "dbo.tblClient";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsClient() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords() {
        String query = "SELECT row_number() over(order by " + tabla + ".idClient) As NUMBER, ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAME ";
        query += "FROM " + tabla;
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text){
        String query = "SELECT row_number() over(order by " + tabla + ".idClient) As NUMBER, ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAME ";
        query += "FROM " + tabla;
        if (!text.Equals("")) {
            query += " WHERE " + tabla + ".sName LIKE '%" + text + "%' ";
        }
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }
    }

    public DataSet SelectEmailClient() {
        string query = "Select p.sEmail ";
        query += "From tblClient c, tblUser u, tblPNatural p ";
        query += "Where c.idUser = u.idUser And u.idPNatural=p.idPNatural";

        return this.ExecuteQuery(query);
    }

    public DataSet ConsultID(String id) {
        String query = "SELECT ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAME ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idClient=" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultUserID(String id) {
        String query = "SELECT ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAMEr ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idUser=" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultPNaturalID(String id) {
        String query = "SELECT ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAMEr ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idPNatural=" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultPLegalID(String id) {
        String query = "SELECT ";
        query += tabla + ".idClient As ID, ";
        query += tabla + ".idPLegal As LEGAL, " + tabla + ".idUser As USERR, ";
        query += tabla + ".sName As NAMEr ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idPLegal=" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert() {
        String query = "INSERT INTO " + tabla;
        query += "(idPLegal, idUser, sName) ";
        query += "VALUES ('{0}','{1}','{2}')";

        String sql = String.Format(query, this.IdPLegal, this.IdUser, this.SName);
        return this.ExecuteDML(sql, "no");
    }

    public int Update() {
        String query = "UPDATE " + tabla + " SET ";
        query += tabla + ".idPLegal=" + this.IdPLegal + ", ";
        query += tabla + ".idUser=" + this.IdUser + ", " + tabla + ".sName='" + this.SName + "' ";
        query += "WHERE ";
        query += tabla + ".idClient=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idClient={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters
        
    public int ID
    {
        get { return idClient; }
        set { idClient = value; }
    }

     public int IdUser {
        get { return idUser; }
        set { idUser = value; }
    }

    public int IdPLegal {
        get { return idPLegal; }
        set { idPLegal = value; }
    }
    public string SName {
        get { return sName; }
        set { sName = value; }
    }
    #endregion

}

#endregion